from common import DirectorEntity,SearchFilter
import DatabaseConnection

class DirectorData(object):
    def __init__(self):
        pass

    def InsertDirector(self,director):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsDirector',(director.Username,director.Bio,director.Major,director.HomeTown,director.HomeState,director.Photo))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdateDirector(self,director):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdDirector',(director.Username,director.Bio,director.Major,director.HomeTown,director.HomeState,director.Photo))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteDirector(self,ID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelDirector',(ID,)) #ID is actually username
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type DirectorEntity or None (if there is no record with this ID)
    def SelectDirectorByID(self,ID):
        query = """ SELECT Username,Bio,Major,HomeTown,HomeState,Photo,Name,Gender,Email,Address,BirthDate,Phone,RegDate
                    FROM vwDirector
                    WHERE Username='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return DirectorEntity.DirectorEntity(username=result[0][0],bio=result[0][1],major=result[0][2],homeTown=result[0][3],homeState=result[0][4],\
                                                 photo=result[0][5],name=result[0][6],gender=result[0][7],email=result[0][8],address=result[0][9],\
                                                 birthDate=result[0][10],phone=result[0][11],regDate=result[0][12])
        else:
            return None

    #This method returns a list of objects of type DirectorEntity
    def SelectDirectorByFilter(self,searchFilter):
        query = """ SELECT Username,Bio,Major,HomeTown,HomeState,Photo,Name,Gender,Email,Address,BirthDate,Phone,RegDate
                    FROM vwDirector"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(DirectorEntity.DirectorEntity(username=record[0],bio=record[1],major=record[2],homeTown=record[3],homeState=record[4],\
                                                           photo=record[5],name=record[6],gender=record[7],email=record[8],address=record[9],\
                                                           birthDate=record[10],phone=record[11],regDate=record[12]))
        return recordSet

    def SelectAllDirectors(self):
        query = """ SELECT Username,Bio,Major,HomeTown,HomeState,Photo,Name,Gender,Email,Address,BirthDate,Phone,RegDate
                    FROM vwDirector"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(DirectorEntity.DirectorEntity(username=record[0],bio=record[1],major=record[2],homeTown=record[3],homeState=record[4],\
                                                           photo=record[5],name=record[6],gender=record[7],email=record[8],address=record[9],\
                                                           birthDate=record[10],phone=record[11],regDate=record[12]))
        return recordSet
